Indexing question

Hi all,

I would like to create an index to speed up the following query:

SELECT Account, Status, count(*) From MessageStatus WHERE sentDate >
'(variable)' AND sentDate < '(variable)' GROUP BY Account,Status ORDER BY
Account

sentDate is a timestamp(14), Account and Status are both varchars.

The table also contains another couple of columns and already has an index
on sentDate.

So two questions:

1. What index should I create (my original guess was just sentDate, but now
I am thinking sentDate(8),Account,Status)

2. Does the fact that I created the sentDate index, which is not being used
as it does not speed up the query, detriment the performance significantly?
Should I remove that index as it is not being used?

The table currently has around 800,000 entries in it and grows by between
4,000 and 100,000 entries a day.

Entries are deleted once they are 6 months old.

Many thanks,

Ben


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Indexing question

Ben

It would appear that the deletion of rows may be a problem.
After deleting rows older than 6 months, do you optimize the
table?

As I understand it, mysql does not delete delete, only marks
a bit for every row thats deleted. That way, delete speed is
fast. However, it slows down queries and inserts. I've tested
this my deleting half of the rows in a table. The before and
after file size size's are equal.

I don't think you need the ORDER BY Account clause. The GROUP
BY Account will automatically sort it. I read it somewhere.

David

> -----Original Message-----
> From: Ben Holness [mailto:ben@bens-house.org.uk]
> Sent: Tuesday, August 27, 2002 1:20 AM
> To: Mysql z_mailing
> Subject: Indexing question
>
>
> Hi all,
>
> I would like to create an index to speed up the following query:
>
> SELECT Account, Status, count(*) From MessageStatus WHERE sentDate >
> '(variable)' AND sentDate < '(variable)' GROUP BY
> Account,Status ORDER BY
> Account
>
> sentDate is a timestamp(14), Account and Status are both varchars.
>
> The table also contains another couple of columns and already
> has an index
> on sentDate.
>
> So two questions:
>
> 1. What index should I create (my original guess was just
> sentDate, but now
> I am thinking sentDate(8),Account,Status)
>
> 2. Does the fact that I created the sentDate index, which is
> not being used
> as it does not speed up the query, detriment the performance
> significantly?
> Should I remove that index as it is not being used?
>
> The table currently has around 800,000 entries in it and
> grows by between
> 4,000 and 100,000 entries a day.
>
> Entries are deleted once they are 6 months old.
>
> Many thanks,
>
> Ben
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail
> To unsubscribe, e-mail
>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Indexing question

--0015175cfb9c7bf2310491b3117f
Content-Type: text/plain; charset=ISO-8859-1

So if you have individual indexes for example field_1, field_2 and field_3
etc and then perform a search like

WHERE field_1 = 10
AND field_3 = 'abc'

This wouldn't improve the search ? You have to create a index for all
possible combined field searches ?



On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey wrote:

> You can't use an index to select records in a range, and order them. The
> order by will cause a filesort in that case.
>
> Additionally indexes are always read left to right. So an index on
> ('user_id', 'product_id') will help when doing WHERE user_id=N AND
> product_id IN (1,2,3), but wouldn't help for just the condtion on
> product_id.
>
> See the manual for full details on how mysql uses indexes:
> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
>
>
> -----Original Message-----
> From: Jonas Galvez [mailto:jonasgalvez@gmail.com]
> Sent: Friday, October 01, 2010 11:48 AM
> To: mysql@lists.mysql.com
> Subject: Indexing question
>
> Suppose I wanted to be able to perform queries against three columns of my
> table: 'user_id', 'product_id' and 'created'. Most of the time I'll just be
> range-selecting records from the table ordering by 'created'. But I may
> also
> want to select where 'user_id' = something and 'product_id' in (list, of,
> ids), ordered by 'created'. Do I need two separate indexes, one on
> 'created'
> and another on ('user_id', 'product_id', 'created'), or does having only
> the
> latter suffice the former case?
>
>
> -- Jonas, http://jonasgalvez.com.br
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you are notified that
> reviewing, disseminating, disclosing, copying or distributing this e-mail is
> strictly prohibited. Please notify the sender immediately by e-mail if you
> have received this e-mail by mistake and delete this e-mail from your
> system. E-mail transmission cannot be guaranteed to be secure or error-free
> as information could be intercepted, corrupted, lost, destroyed, arrive late
> or incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or omissions in
> the contents of this message, which arise as a result of e-mail
> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
> 94089, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>
>

--0015175cfb9c7bf2310491b3117f--

Re: Indexing question

Hi Neil, all!


Tompkins Neil wrote:
> So if you have individual indexes for example field_1, field_2 and fiel=
d_3
> etc and then perform a search like
>=20
> WHERE field_1 =3D 10
> AND field_3 =3D 'abc'
>=20
> This wouldn't improve the search ? You have to create a index for all
> possible combined field searches ?

No - you didn't read Gavin's mail exact enough:

> On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey wrote:
>=20
>> [[...]]
>>
>> Additionally indexes are always read left to right. So an index on
>> ('user_id', 'product_id') will help when doing WHERE user_id=3DN AND
>> product_id IN (1,2,3), but wouldn't help for just the condtion on
>> product_id.

What Gavin calls "left to right" is what I call "most significant
first", the result is the same:

In a multi-column index, the columns are listed in the order of their
significance. Any DBMS (this is not limited to MySQL) can use such an
index only if a condition for the first (=3D most significant) field(s) i=
s
(are) specified.

Example: Assume the index is on fields A, B, and C in that order.

A statement "... where A =3D x and B =3D y and C =3D z" can use the index=

A statement "... where A =3D x and B =3D y" can use the index, limited to=

the first two fields.
A statement "... where A =3D x" can use the index. the first field only.
A statement "... where A =3D x and C =3D z" can also use the index for A,=

but will have to evaluate the condition on C by scanning all records
matching A.

A statement "... where B =3D y and C =3D z" cannot use the index, because=

there is no condition on A.

If there are many searches based on A and C only (not B), and there are
many records matching A with different values of C, then an additional
index on these two columns may be helpful.

Compare the index with a phone book, which (typically) lists the entries
sorted by last name (most significant), then first name, then ... :
If you don't know the last name, you cannot profit from the sorting and
have to scan the wole book.

>>
>> See the manual for full details on how mysql uses indexes:
>> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Indexing question

Thanks for your reply. So should we create individual indexes on each =20=

field or a multiple column index ??



On 3 Oct 2010, at 16:44, Joerg Bruehe wrote:

> Hi Neil, all!
>
>
> Tompkins Neil wrote:
>> So if you have individual indexes for example field_1, field_2 and =20=

>> field_3
>> etc and then perform a search like
>>
>> WHERE field_1 =3D 10
>> AND field_3 =3D 'abc'
>>
>> This wouldn't improve the search ? You have to create a index for =20=

>> all
>> possible combined field searches ?
>
> No - you didn't read Gavin's mail exact enough:
>
>> On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey wrote:
>>
>>> [[...]]
>>>
>>> Additionally indexes are always read left to right. So an index on
>>> ('user_id', 'product_id') will help when doing WHERE user_id=3DN AND
>>> product_id IN (1,2,3), but wouldn't help for just the condtion on
>>> product_id.
>
> What Gavin calls "left to right" is what I call "most significant
> first", the result is the same:
>
> In a multi-column index, the columns are listed in the order of their
> significance. Any DBMS (this is not limited to MySQL) can use such an
> index only if a condition for the first (=3D most significant) field=20=

> (s) is
> (are) specified.
>
> Example: Assume the index is on fields A, B, and C in that order.
>
> A statement "... where A =3D x and B =3D y and C =3D z" can use the =
index.
> A statement "... where A =3D x and B =3D y" can use the index, limited =
to
> the first two fields.
> A statement "... where A =3D x" can use the index. the first field =
only.
> A statement "... where A =3D x and C =3D z" can also use the index for =
A,
> but will have to evaluate the condition on C by scanning all records
> matching A.
>
> A statement "... where B =3D y and C =3D z" cannot use the index, =
because
> there is no condition on A.
>
> If there are many searches based on A and C only (not B), and there =20=

> are
> many records matching A with different values of C, then an additional
> index on these two columns may be helpful.
>
> Compare the index with a phone book, which (typically) lists the =20
> entries
> sorted by last name (most significant), then first name, then ... :
> If you don't know the last name, you cannot profit from the sorting =20=

> and
> have to scan the wole book.
>
>>>
>>> See the manual for full details on how mysql uses indexes:
>>> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
>
> HTH,
> Jörg
>
> --=20
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 =20
> Berlin
> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
> Amtsgericht Muenchen: HRA 95603
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Indexing question

Following on from my previous email I have columns containing numbers =20=

which are then used in SUM and MIN/ MAX functions should these be =20
indexed too ?

On 3 Oct 2010, at 16:44, Joerg Bruehe wrote:

> Hi Neil, all!
>
>
> Tompkins Neil wrote:
>> So if you have individual indexes for example field_1, field_2 and =20=

>> field_3
>> etc and then perform a search like
>>
>> WHERE field_1 =3D 10
>> AND field_3 =3D 'abc'
>>
>> This wouldn't improve the search ? You have to create a index for =20=

>> all
>> possible combined field searches ?
>
> No - you didn't read Gavin's mail exact enough:
>
>> On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey wrote:
>>
>>> [[...]]
>>>
>>> Additionally indexes are always read left to right. So an index on
>>> ('user_id', 'product_id') will help when doing WHERE user_id=3DN AND
>>> product_id IN (1,2,3), but wouldn't help for just the condtion on
>>> product_id.
>
> What Gavin calls "left to right" is what I call "most significant
> first", the result is the same:
>
> In a multi-column index, the columns are listed in the order of their
> significance. Any DBMS (this is not limited to MySQL) can use such an
> index only if a condition for the first (=3D most significant) field=20=

> (s) is
> (are) specified.
>
> Example: Assume the index is on fields A, B, and C in that order.
>
> A statement "... where A =3D x and B =3D y and C =3D z" can use the =
index.
> A statement "... where A =3D x and B =3D y" can use the index, limited =
to
> the first two fields.
> A statement "... where A =3D x" can use the index. the first field =
only.
> A statement "... where A =3D x and C =3D z" can also use the index for =
A,
> but will have to evaluate the condition on C by scanning all records
> matching A.
>
> A statement "... where B =3D y and C =3D z" cannot use the index, =
because
> there is no condition on A.
>
> If there are many searches based on A and C only (not B), and there =20=

> are
> many records matching A with different values of C, then an additional
> index on these two columns may be helpful.
>
> Compare the index with a phone book, which (typically) lists the =20
> entries
> sorted by last name (most significant), then first name, then ... :
> If you don't know the last name, you cannot profit from the sorting =20=

> and
> have to scan the wole book.
>
>>>
>>> See the manual for full details on how mysql uses indexes:
>>> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
>
> HTH,
> Jörg
>
> --=20
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 =20
> Berlin
> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
> Amtsgericht Muenchen: HRA 95603
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Indexing question

Hi!


Neil Tompkins wrote:
> Thanks for your reply. So should we create individual indexes on each
> field or a multiple column index ??

This question cannot be answered without checking and measuring your
installation. The decision whether to create an index is always an act
of balancing:

- If there is an index, the database server can use it to find data
records by looking up the index, not scanning the base data.
This results in load reduction (both CPU and disk IO) and speeds up
query execution.

- If there is an index, the database server must maintain it whenever
data are altered (insert/update/delete), in addition to the base data.
This is increased load (both CPU and disk IO) and slows down data
changes.

So obviously you want to create only those indexes that are helpful for
query execution: you will never (voluntarily) create an index on a
column which isn't used in search conditions, or whose use is already
provided by other indexes.
Of the remaining candidate indexes, you will never (voluntarily) create
one that provides less gain in searches than it costs in data changes.

With MySQL, AFAIK there is the limitation that on one table only one
index can be used. As a result, the choice of indexes to create depends
on the searches executed by your commands, their relative frequency, and
the frequency of data changes.


To answer your other question: If you run aggregate functions (like
SUM(), MIN(), or MAX()) on all records of a table, their results could
be computed by accessing a matching index only. I don't know whether
MySQL does this, I propose you check that yourself using EXPLAIN.

If you run them on subsets of a table only, an index on that column will
not help in general.

In database implementations, there is the concept of a "covering index":
If you have an index on columns A and B of some table, its contents
(without the base data) would suffice to answer
SELECT SUM(B) WHERE A =3D x
Again, I don't know whether MySQL does this, and I refer you to EXPLAIN.


HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Indexing question

--0016e644c7a6fce0bc0491cc8da6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Jörg

Thanks for the useful reply. Maybe I can EXPLAIN my select queries for you
to advise if any changes need to be made ?

Regards
Neil

On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe wrote=
:

> Hi!
>
>
> Neil Tompkins wrote:
> > Thanks for your reply. So should we create individual indexes on each
> > field or a multiple column index ??
>
> This question cannot be answered without checking and measuring your
> installation. The decision whether to create an index is always an act
> of balancing:
>
> - If there is an index, the database server can use it to find data
> records by looking up the index, not scanning the base data.
> This results in load reduction (both CPU and disk IO) and speeds up
> query execution.
>
> - If there is an index, the database server must maintain it whenever
> data are altered (insert/update/delete), in addition to the base data.
> This is increased load (both CPU and disk IO) and slows down data
> changes.
>
> So obviously you want to create only those indexes that are helpful for
> query execution: you will never (voluntarily) create an index on a
> column which isn't used in search conditions, or whose use is already
> provided by other indexes.
> Of the remaining candidate indexes, you will never (voluntarily) create
> one that provides less gain in searches than it costs in data changes.
>
> With MySQL, AFAIK there is the limitation that on one table only one
> index can be used. As a result, the choice of indexes to create depends
> on the searches executed by your commands, their relative frequency, and
> the frequency of data changes.
>
>
> To answer your other question: If you run aggregate functions (like
> SUM(), MIN(), or MAX()) on all records of a table, their results could
> be computed by accessing a matching index only. I don't know whether
> MySQL does this, I propose you check that yourself using EXPLAIN.
>
> If you run them on subsets of a table only, an index on that column will
> not help in general.
>
> In database implementations, there is the concept of a "covering index":
> If you have an index on columns A and B of some table, its contents
> (without the base data) would suffice to answer
> SELECT SUM(B) WHERE A =3D x
> Again, I don't know whether MySQL does this, and I refer you to EXPLAIN.
>
>
> HTH,
> Jörg
>
> --
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
> Amtsgericht Muenchen: HRA 95603
>
>

--0016e644c7a6fce0bc0491cc8da6--

RE: Indexing question

Include the query, EXPLAIN output, and the relavant SHOW CREATE TABLE table=
\G output. Someone should be able to offer suggestions.

-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
Sent: Monday, October 04, 2010 8:54 AM
To: Joerg Bruehe
Cc: [MySQL]
Subject: Re: Indexing question

Jörg

Thanks for the useful reply. Maybe I can EXPLAIN my select queries for you
to advise if any changes need to be made ?

Regards
Neil

On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe wrote=
:

> Hi!
>
>
> Neil Tompkins wrote:
> > Thanks for your reply. So should we create individual indexes on each
> > field or a multiple column index ??
>
> This question cannot be answered without checking and measuring your
> installation. The decision whether to create an index is always an act
> of balancing:
>
> - If there is an index, the database server can use it to find data
> records by looking up the index, not scanning the base data.
> This results in load reduction (both CPU and disk IO) and speeds up
> query execution.
>
> - If there is an index, the database server must maintain it whenever
> data are altered (insert/update/delete), in addition to the base data.
> This is increased load (both CPU and disk IO) and slows down data
> changes.
>
> So obviously you want to create only those indexes that are helpful for
> query execution: you will never (voluntarily) create an index on a
> column which isn't used in search conditions, or whose use is already
> provided by other indexes.
> Of the remaining candidate indexes, you will never (voluntarily) create
> one that provides less gain in searches than it costs in data changes.
>
> With MySQL, AFAIK there is the limitation that on one table only one
> index can be used. As a result, the choice of indexes to create depends
> on the searches executed by your commands, their relative frequency, and
> the frequency of data changes.
>
>
> To answer your other question: If you run aggregate functions (like
> SUM(), MIN(), or MAX()) on all records of a table, their results could
> be computed by accessing a matching index only. I don't know whether
> MySQL does this, I propose you check that yourself using EXPLAIN.
>
> If you run them on subsets of a table only, an index on that column will
> not help in general.
>
> In database implementations, there is the concept of a "covering index":
> If you have an index on columns A and B of some table, its contents
> (without the base data) would suffice to answer
> SELECT SUM(B) WHERE A =3D x
> Again, I don't know whether MySQL does this, and I refer you to EXPLAIN.
>
>
> HTH,
> Jörg
>
> --
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
> Amtsgericht Muenchen: HRA 95603
>
>

This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Indexing question

I've got a fair few number of queries to be checked over. Will send =20
them tommorrow

On 4 Oct 2010, at 18:27, Gavin Towey wrote:

> Include the query, EXPLAIN output, and the relavant SHOW CREATE =20
> TABLE table \G output. Someone should be able to offer suggestions.
>
> -----Original Message-----
> From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> Sent: Monday, October 04, 2010 8:54 AM
> To: Joerg Bruehe
> Cc: [MySQL]
> Subject: Re: Indexing question
>
> Jörg
>
> Thanks for the useful reply. Maybe I can EXPLAIN my select queries =20=

> for you
> to advise if any changes need to be made ?
>
> Regards
> Neil
>
> On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe =20
> wrote:
>
>> Hi!
>>
>>
>> Neil Tompkins wrote:
>>> Thanks for your reply. So should we create individual indexes on =20
>>> each
>>> field or a multiple column index ??
>>
>> This question cannot be answered without checking and measuring your
>> installation. The decision whether to create an index is always an =20=

>> act
>> of balancing:
>>
>> - If there is an index, the database server can use it to find data
>> records by looking up the index, not scanning the base data.
>> This results in load reduction (both CPU and disk IO) and speeds up
>> query execution.
>>
>> - If there is an index, the database server must maintain it whenever
>> data are altered (insert/update/delete), in addition to the base =20
>> data.
>> This is increased load (both CPU and disk IO) and slows down data
>> changes.
>>
>> So obviously you want to create only those indexes that are helpful =20=

>> for
>> query execution: you will never (voluntarily) create an index on a
>> column which isn't used in search conditions, or whose use is already
>> provided by other indexes.
>> Of the remaining candidate indexes, you will never (voluntarily) =20
>> create
>> one that provides less gain in searches than it costs in data =20
>> changes.
>>
>> With MySQL, AFAIK there is the limitation that on one table only one
>> index can be used. As a result, the choice of indexes to create =20
>> depends
>> on the searches executed by your commands, their relative =20
>> frequency, and
>> the frequency of data changes.
>>
>>
>> To answer your other question: If you run aggregate functions (like
>> SUM(), MIN(), or MAX()) on all records of a table, their results =20
>> could
>> be computed by accessing a matching index only. I don't know whether
>> MySQL does this, I propose you check that yourself using EXPLAIN.
>>
>> If you run them on subsets of a table only, an index on that column =20=

>> will
>> not help in general.
>>
>> In database implementations, there is the concept of a "covering =20
>> index":
>> If you have an index on columns A and B of some table, its contents
>> (without the base data) would suffice to answer
>> SELECT SUM(B) WHERE A =3D x
>> Again, I don't know whether MySQL does this, and I refer you to =20
>> EXPLAIN.
>>
>>
>> HTH,
>> Jörg
>>
>> --
>> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
>> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 =20
>> Berlin
>> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. =20=

>> Ven
>> Amtsgericht Muenchen: HRA 95603
>>
>>
>
> This message contains confidential information and is intended only =20=

> for the individual named. If you are not the named addressee, you =20
> are notified that reviewing, disseminating, disclosing, copying or =20
> distributing this e-mail is strictly prohibited. Please notify the =20=

> sender immediately by e-mail if you have received this e-mail by =20
> mistake and delete this e-mail from your system. E-mail transmission =20=

> cannot be guaranteed to be secure or error-free as information could =20=

> be intercepted, corrupted, lost, destroyed, arrive late or =20
> incomplete, or contain viruses. The sender therefore does not accept =20=

> liability for any loss or damage caused by viruses or errors or =20
> omissions in the contents of this message, which arise as a result =20
> of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt =20
> court, Sunnyvale, CA 94089, USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Indexing question

--0015175cb1d45de03e0491daa300
Content-Type: multipart/alternative; boundary=0015175cb1d45de0380491daa3fe

--0015175cb1d45de0380491daa3fe
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi

Find attached the first of my queries, I require to be checked over. Let m=
e
know if you need any more information, I've included the query, EXPLAIN
output, and the relavant SHOW CREATE TABLE

Regards
Neil

On Mon, Oct 4, 2010 at 9:50 PM, Neil Tompkins
wrote:

> I've got a fair few number of queries to be checked over. Will send them
> tommorrow
>
>
> On 4 Oct 2010, at 18:27, Gavin Towey wrote:
>
> Include the query, EXPLAIN output, and the relavant SHOW CREATE TABLE
>> table \G output. Someone should be able to offer suggestions.
>>
>> -----Original Message-----
>> From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
>> Sent: Monday, October 04, 2010 8:54 AM
>> To: Joerg Bruehe
>> Cc: [MySQL]
>> Subject: Re: Indexing question
>>
>> Jörg
>>
>> Thanks for the useful reply. Maybe I can EXPLAIN my select queries for
>> you
>> to advise if any changes need to be made ?
>>
>> Regards
>> Neil
>>
>> On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe >> >wrote:
>>
>> Hi!
>>>
>>>
>>> Neil Tompkins wrote:
>>>
>>>> Thanks for your reply. So should we create individual indexes on each
>>>> field or a multiple column index ??
>>>>
>>>
>>> This question cannot be answered without checking and measuring your
>>> installation. The decision whether to create an index is always an act
>>> of balancing:
>>>
>>> - If there is an index, the database server can use it to find data
>>> records by looking up the index, not scanning the base data.
>>> This results in load reduction (both CPU and disk IO) and speeds up
>>> query execution.
>>>
>>> - If there is an index, the database server must maintain it whenever
>>> data are altered (insert/update/delete), in addition to the base data.
>>> This is increased load (both CPU and disk IO) and slows down data
>>> changes.
>>>
>>> So obviously you want to create only those indexes that are helpful for
>>> query execution: you will never (voluntarily) create an index on a
>>> column which isn't used in search conditions, or whose use is already
>>> provided by other indexes.
>>> Of the remaining candidate indexes, you will never (voluntarily) create
>>> one that provides less gain in searches than it costs in data changes.
>>>
>>> With MySQL, AFAIK there is the limitation that on one table only one
>>> index can be used. As a result, the choice of indexes to create depends
>>> on the searches executed by your commands, their relative frequency, an=
d
>>> the frequency of data changes.
>>>
>>>
>>> To answer your other question: If you run aggregate functions (like
>>> SUM(), MIN(), or MAX()) on all records of a table, their results could
>>> be computed by accessing a matching index only. I don't know whether
>>> MySQL does this, I propose you check that yourself using EXPLAIN.
>>>
>>> If you run them on subsets of a table only, an index on that column wil=
l
>>> not help in general.
>>>
>>> In database implementations, there is the concept of a "covering index"=
:
>>> If you have an index on columns A and B of some table, its contents
>>> (without the base data) would suffice to answer
>>> SELECT SUM(B) WHERE A =3D x
>>> Again, I don't know whether MySQL does this, and I refer you to EXPLAIN=
..
>>>
>>>
>>> HTH,
>>> Jörg
>>>
>>> --
>>> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
>>> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
>>> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
>>> Amtsgericht Muenchen: HRA 95603
>>>
>>>
>>>
>> This message contains confidential information and is intended only for
>> the individual named. If you are not the named addressee, you are notif=
ied
>> that reviewing, disseminating, disclosing, copying or distributing this
>> e-mail is strictly prohibited. Please notify the sender immediately by
>> e-mail if you have received this e-mail by mistake and delete this e-mai=
l
>> from your system. E-mail transmission cannot be guaranteed to be secure =
or
>> error-free as information could be intercepted, corrupted, lost, destroy=
ed,
>> arrive late or incomplete, or contain viruses. The sender therefore does=
not
>> accept liability for any loss or damage caused by viruses or errors or
>> omissions in the contents of this message, which arise as a result of e-=
mail
>> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale=
, CA
>> 94089, USA, FriendFinder.com
>>
>

--0015175cb1d45de0380491daa3fe
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi

Re: Indexing question

--0016e6509b508a7be90491f986fc
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks Gavin and Joerg, that was very helpful!

-- Jonas

On Sun, Oct 3, 2010 at 12:44 PM, Joerg Bruehe wrot=
e:

> Hi Neil, all!
>
>
> Tompkins Neil wrote:
> > So if you have individual indexes for example field_1, field_2 and
> field_3
> > etc and then perform a search like
> >
> > WHERE field_1 =3D 10
> > AND field_3 =3D 'abc'
> >
> > This wouldn't improve the search ? You have to create a index for all
> > possible combined field searches ?
>
> No - you didn't read Gavin's mail exact enough:
>
> > On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey wrote:
> >
> >> [[...]]
> >>
> >> Additionally indexes are always read left to right. So an index on
> >> ('user_id', 'product_id') will help when doing WHERE user_id=3DN AND
> >> product_id IN (1,2,3), but wouldn't help for just the condtion on
> >> product_id.
>
> What Gavin calls "left to right" is what I call "most significant
> first", the result is the same:
>
> In a multi-column index, the columns are listed in the order of their
> significance. Any DBMS (this is not limited to MySQL) can use such an
> index only if a condition for the first (=3D most significant) field(s) i=
s
> (are) specified.
>
> Example: Assume the index is on fields A, B, and C in that order.
>
> A statement "... where A =3D x and B =3D y and C =3D z" can use the index=
..
> A statement "... where A =3D x and B =3D y" can use the index, limited to
> the first two fields.
> A statement "... where A =3D x" can use the index. the first field only.
> A statement "... where A =3D x and C =3D z" can also use the index for A,
> but will have to evaluate the condition on C by scanning all records
> matching A.
>
> A statement "... where B =3D y and C =3D z" cannot use the index, because
> there is no condition on A.
>
> If there are many searches based on A and C only (not B), and there are
> many records matching A with different values of C, then an additional
> index on these two columns may be helpful.
>
> Compare the index with a phone book, which (typically) lists the entries
> sorted by last name (most significant), then first name, then ... :
> If you don't know the last name, you cannot profit from the sorting and
> have to scan the wole book.
>
> >>
> >> See the manual for full details on how mysql uses indexes:
> >> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
>
> HTH,
> Jörg
>
> --
> Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
> ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
> Amtsgericht Muenchen: HRA 95603
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Djonasgalvez@gmail.com
>
>

--0016e6509b508a7be90491f986fc--